﻿using AutoMapper;
using ClosedXML.Excel;
using Dapper;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Office2016.Excel;
using MediatR;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MyMES.Domain.zmc;
using MyMES.Error;
using MyMES.Infrastructure;
using MyMES.Infrastructure.Interface;
using MyMES.Writer.API.Application.Commends.Customers;
using NPOI.SS.UserModel;
using Org.BouncyCastle.Asn1.Ocsp;
using System.Data;
using System.Text.RegularExpressions;
using System.Text;

namespace MyMES.Writer.API.Controllers.Customers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class CustomersController : ControllerBase
    {

        private readonly IMediator mediator;

        private readonly IBaseRepository<Customermanagement> customerRepository;
        private readonly IBaseRepository<IMapper> mapper;
        public DatabaseContext db;
  




        public CustomersController(IMediator mediator, IBaseRepository<Customermanagement> customerRepository, IBaseRepository<IMapper> mapper, DatabaseContext db)
        {
            this.mediator = mediator;
            this.customerRepository = customerRepository;
            this.mapper = mapper;
            this.db = db;
        }


        /// <summary>
        /// 添加客户信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPost]
        public async Task<APIResult<Customermanagement>> CreateCustomers(CreateCustomersCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 更新客户信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Customermanagement>> UpdataCustomers(UpdataCustomersCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        /// 
        [HttpPut]
        public async Task<APIResult<Customermanagement>> DelCustomers(DelCustomersCommends request, CancellationToken cancellationToken)
        {
            return await mediator.Send(request, cancellationToken);
        }

        /// <summary>
        /// 导入客户信息
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        /*   [HttpPost]
           public async Task<IActionResult> ImportInventories(IFormFile file)
           {
               APIResult<int> result = new APIResult<int>();
               try
               {
                   // 检查上传文件是否为空
                   if (file == null || file.Length <= 0)
                   {
                       result.Code = APIEnum.查询失败;
                       result.Msg = "未上传文件或文件为空";

                   }

                   // 检查文件扩展名是否为 Excel 格式
                   var fileExtension = Path.GetExtension(file.FileName);
                   if (fileExtension != ".xlsx" && fileExtension != ".xls")
                   {
                       result.Code = APIEnum.查询失败;
                       result.Msg = "仅支持上传 Excel 文件";

                   }

                   // 使用 MemoryStream 读取文件内容
                   using (var stream = new MemoryStream())
                   {
                       await file.CopyToAsync(stream);
                       using (var workbook = new XLWorkbook(stream))
                       {
                           var worksheet = workbook.Worksheet(1); // 获取第一个工作表
                           var rows = worksheet.RowsUsed().Skip(1); // 跳过第一行（表头）

                           List<Customermanagement> inventoriesList = new List<Customermanagement>();

                           foreach (var row in rows)
                           {



                               var inventory = new Customermanagement();
                               inventory.CustomerNo = row.Cell(1).GetValue<string>();
                               inventory.CustomerName = row.Cell(2).GetValue<string>();
                               inventory.Customerabbrevias = row.Cell(3).GetValue<string>();
                               inventory.Customerphone = row.Cell(4).GetValue<string>();
                               inventory.Contactone = row.Cell(5).GetValue<string>();
                               inventory.Contactphoneone = row.Cell(6).GetValue<string>();
                            //   inventory.Isok = row.Cell(7).GetValue<bool>();



                               // 添加到要插入的列表中
                               inventoriesList.Add(inventory);


                               string sql = "INSERT INTO Customermanagement (CustomerNo,CustomerName,Customerabbrevias,Customerphone,Contactone,Contactphoneone) VALUES (@CustomerNo,@CustomerName,@Customerabbrevias,@Customerphone,@Contactone,@Contactphoneone)";

                                await customerRepository.Insertobject(inventoriesList, sql);
                            *//*   using (IDbConnection conn = db.Connection)
                               {
                                   conn.Open();
                                   await conn.Execute(sql,);
                               }*//*

                                   result.Code = APIEnum.添加成功;
                               result.Msg = "导入成功";

                           }
                       }
                   }
               }
               catch (Exception ex)
               {

                   result.Code = APIEnum.添加失败;
                   result.Msg = "导入失败，原因：" + ex.Message;

               }
               return Ok(result);
           }*/





        /// <summary>
        /// 导入客户信息
        /// </summary>
        [HttpPost]
        public async Task<IActionResult> ImportCustomers(IFormFile file)
        {
            var result = new APIResult<int>();
            IDbTransaction transaction = null;
            var connection = db.Connection;
            var errorLogs = new StringBuilder();

            try
            {
                // ==================== 1. 文件基础验证 ====================
                if (file == null || file.Length == 0)
                    return Ok(new APIResult<int> { Code = APIEnum.查询失败, Msg = "请上传有效文件" });

                if (!Path.GetExtension(file.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
                    return Ok(new APIResult<int> { Code = APIEnum.查询失败, Msg = "仅支持.xlsx格式" });

                using var stream = new MemoryStream();
                await file.CopyToAsync(stream);
                using var workbook = new XLWorkbook(stream);
                var worksheet = workbook.Worksheet(1);
                var rows = worksheet.RowsUsed().Skip(1); // 跳过标题行

                connection.Open();
                transaction = connection.BeginTransaction();

                var customers = new List<Customermanagement>();
                var rowNumber = 1;

                // ==================== 3. 处理Excel数据行11 ====================
                foreach (var row in rows)
                {
                    rowNumber++;
                    try
                    {
                        // ==================== 2. 预加载客户类型数据 ====================
                        var customerTypes = await connection.QueryAsync<CustomerTypes>(
                            "SELECT CustomerTypeId, CustomerTypeName FROM CustomerTypes",
                            transaction: transaction);

                        var typeDict = customerTypes
                            .GroupBy(t => t.CustomerTypeName.Trim(), StringComparer.OrdinalIgnoreCase)
                            .ToDictionary(
                                g => g.Key,
                                g => {
                                    if (g.Count() > 1)
                                        throw new Exception($"存在重复客户类型: {g.Key}");
                                    return g.First().CustomerTypeId;
                                },
                                StringComparer.OrdinalIgnoreCase
                            );
                        // ===== 3.2 客户类型处理 =====
                        var CustomerTypeName = GetCellString(row.Cell(8)); // G列
                        if (!typeDict.TryGetValue(CustomerTypeName, out var CustomerTypeId))
                            throw new Exception($"客户类型'{CustomerTypeName}'不存在");
                   
                        // ===== 3.1 基础数据读取 =====
                        var customer = new Customermanagement
                        {
                            CustomerNo = GetCellString(row.Cell(1)),         // A列
                            CustomerName = GetCellString(row.Cell(2)),       // B列
                            Customerabbrevias = GetCellString(row.Cell(3)), // C列
                            Customerphone = GetCellString(row.Cell(4)),      // D列
                            Contactone = GetCellString(row.Cell(5)),         // E列
                            Contactphoneone = GetCellString(row.Cell(6)),    // F列
                            Isok= ParseBool(row.Cell(7).GetValue<string>()), // 第7列是布尔值列
                            CustomerTypeId= CustomerTypeId,
                            IsDlete = ParseBool(row.Cell(9).GetValue<string>()) // 第6列是布尔值列
                        };

               

                        // ===== 3.3 关键数据验证 =====
                        if (string.IsNullOrWhiteSpace(customer.CustomerNo))
                            throw new Exception("客户编码不能为空");

                        if (string.IsNullOrWhiteSpace(customer.CustomerName))
                            throw new Exception("客户名称不能为空");

                        customers.Add(customer);
                    }
                    catch (Exception ex)
                    {
                        errorLogs.AppendLine($"第 {rowNumber} 行错误: {ex.Message}");
                    }
                }

                // ==================== 4. 统一错误处理 ====================
                if (errorLogs.Length > 0)
                {
                    transaction.Rollback();
                    return Ok(new APIResult<int>
                    {
                        Code = APIEnum.添加失败,
                        Msg = $"发现 {errorLogs.ToString().Count(c => c == '\n')} 个错误",
                       // data = errorLogs.ToString()
                    });
                }

                // ==================== 5. 批量插入数据 ====================
                const string sql = @"INSERT INTO Customermanagement 
            (CustomerNo, CustomerName, Customerabbrevias, Customerphone, 
             Contactone, Contactphoneone, Isok,CustomerTypeId,IsDlete)
            VALUES 
            (@CustomerNo, @CustomerName, @Customerabbrevias, @Customerphone,
             @Contactone, @Contactphoneone, @Isok,@CustomerTypeId,@IsDlete)";

                var affectedRows = await connection.ExecuteAsync(sql, customers, transaction);
                transaction.Commit();

                result.Code = APIEnum.添加成功;
                result.Msg = $"成功导入 {affectedRows} 条客户信息";
                result.data = affectedRows;
            }
            catch (Exception ex)
            {
                transaction?.Rollback();
                result.Code = APIEnum.添加失败;
                result.Msg = $"系统错误: {ex.Message}";
                // 实际项目应记录日志
                // _logger.LogError(ex, "客户导入异常");
            }
            finally
            {
                connection?.Close();
            }

            return Ok(result);
        }

        // ==================== 辅助方法 ====================

        /// <summary>
        /// 安全获取字符串值（自动Trim处理）
        /// </summary>
        private string GetCellString(IXLCell cell)
        {
            return cell.GetValue<string>()?.Trim() ?? string.Empty;
        }

        /// <summary>
        /// 解析布尔值
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <exception cref="FormatException"></exception>
        private bool ParseBool(string value)
        {
            if (string.IsNullOrWhiteSpace(value)) return false;

            var normalizedValue = value.Trim().ToLower();

            return normalizedValue switch
            {
                // 支持正例
                "true" or "1" or "是" or "yes" or "y" => true,
                // 支持反例
                "false" or "0" or "否" or "no" or "n" => false,
                // 明确错误提示
                _ => throw new FormatException($"无效的布尔值: '{value}'，允许的值：是/否、true/false、1/0")
            };
        }

    }
}
